﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using Riel_Gestion.entities;

namespace Riel_Gestion.dao
{
    class VentaDAO
    {
        private static String SQL_SELECT = "SELECT FECHAHORA, DESCRIPCION, IMPORTEVENTA, IMPORTEPAGO, SALDO FROM VENTA WHERE CODCLIENTE = ?";
        private static String SQL_SELECT_SALDOS_BY_ID = "SELECT  SUM(IMPORTEVENTA) AS TOTDEBE, SUM(IMPORTEPAGO) AS TOTHABER FROM VENTA WHERE CODCLIENTE = ?";
        private static String SQL_SELECT_SALDOS_BY_DATES = "SELECT  SUM(IMPORTEVENTA) AS TOTDEBE, SUM(IMPORTEPAGO) AS TOTHABER, SUM(SALDO) AS TOTSALDO FROM VENTA WHERE FECHAHORA BETWEEN @Desde AND @Hasta AND CODCLIENTE <> 0";
        private static String SQL_SELECT_SALDOS_BY_DATES_AND_CLIENTS = "SELECT  SUM(IMPORTEVENTA) AS TOTDEBE, SUM(IMPORTEPAGO) AS TOTHABER, SUM(SALDO) AS TOTSALDO FROM VENTA WHERE FECHAHORA >= ? AND FECHAHORA <= ? AND CODCLIENTE >= ?";
        private static OleDbConnection conn;
        private static OleDbCommand cmd;
        private static OleDbDataAdapter adapter;
        private static OleDbDataReader reader;
        private static OleDbCommandBuilder cmdbl;
        private static String EMPTY_FIELD = "";
        private static DataTable ventas;

        public static DataTable getVentasById(String idCliente)
        {           
            try
            {
                conn = conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["rielConnectionString"].ConnectionString);
                cmd = new OleDbCommand(SQL_SELECT, conn);
                conn.Open();

                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("CODCLIENTE", idCliente);

                cmd.CommandType = CommandType.Text;
                adapter = new OleDbDataAdapter(cmd);
                ventas = new DataTable();
                adapter.Fill(ventas);

                cmdbl = new OleDbCommandBuilder(adapter);
                
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ventas;
        }

        public static DTOSaldo getSaldosTotalesById(String idCliente)
        {
            DTOSaldo saldo = null;
            String value = "";
            try
            {
                saldo = new DTOSaldo(idCliente);
                conn = conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["rielConnectionString"].ConnectionString);
                cmd = new OleDbCommand(SQL_SELECT_SALDOS_BY_ID, conn);
                conn.Open();

                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("CODCLIENTE", idCliente);

                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    value = reader["TOTDEBE"].ToString();
                    if (!EMPTY_FIELD.Equals(value))
                        saldo.TotDebe = Decimal.Parse(value);

                    value = reader["TOTHABER"].ToString();
                    if (!EMPTY_FIELD.Equals(value))
                        saldo.TotHaber = Decimal.Parse(value);

                    saldo.TotSaldo = saldo.TotDebe - saldo.TotHaber;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return saldo;
        }

        public static void save(DataGridView dtgv)
        {            
            foreach (DataGridViewRow row in dtgv.Rows)
            {
                if (!row.IsNewRow)
                {
                    MessageBox.Show(row.Cells[0].Value.ToString() + "','" + row.Cells[1].Value.ToString() + "','" + row.Cells[2].Value.ToString());

                }
            } 
        }



        public static DTOSaldo getSaldosTotalesByDates(DateTime fDesde, DateTime fHasta)
        {
            DTOSaldo saldo = null;
            String value = "";
            try
            {
                saldo = new DTOSaldo();
                conn = conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["rielConnectionString"].ConnectionString);
                cmd = new OleDbCommand(SQL_SELECT_SALDOS_BY_DATES, conn);
                conn.Open();

                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@Desde", fDesde);
                cmd.Parameters.AddWithValue("@Hasta", fHasta);

                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    value = reader["TOTDEBE"].ToString();
                    if (!EMPTY_FIELD.Equals(value))
                        saldo.TotDebe = Decimal.Parse(value);

                    value = reader["TOTHABER"].ToString();
                    if (!EMPTY_FIELD.Equals(value))
                        saldo.TotHaber = Decimal.Parse(value);

                    saldo.TotSaldo = saldo.TotDebe - saldo.TotHaber;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return saldo;
        }



        public static DTOSaldo getSaldosTotalesByDatesAndClients(DateTime fDesde, DateTime fHasta, String cliDesde, String cliHasta)
        {
            DTOSaldo saldo = null;
            String value = "";
            try
            {
                saldo = new DTOSaldo();
                conn = conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["rielConnectionString"].ConnectionString);
                cmd = new OleDbCommand(SQL_SELECT_SALDOS_BY_DATES_AND_CLIENTS, conn);
                conn.Open();

                cmd.CommandType = CommandType.Text;

                cmd.Parameters.AddWithValue("", fDesde);
                cmd.Parameters.AddWithValue("", fHasta);
                cmd.Parameters.AddWithValue("", long.Parse(cliDesde));
                //cmd.Parameters.AddWithValue("", cliHasta);


                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    value = reader["TOTDEBE"].ToString();
                    if (!EMPTY_FIELD.Equals(value))
                        saldo.TotDebe = Decimal.Parse(value);

                    value = reader["TOTHABER"].ToString();
                    if (!EMPTY_FIELD.Equals(value))
                        saldo.TotHaber = Decimal.Parse(value);

                    saldo.TotSaldo = saldo.TotDebe - saldo.TotHaber;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return saldo;
        }
    }
}
